use anyhow::anyhow;
use chrono::NaiveDate;

use crate::{
    base::{get_ch_pool, DiviFactConv, Table},
    divi_back::DiviJoin,
};

pub struct AcblProcess<'a> {
    tconf: &'a Table,
}
impl<'a> AcblProcess<'a> {
    pub fn new(tconf: &'a Table) -> Self {
        Self { tconf }
    }
    ///折叠指定日期的数据，整体清0
    async fn collapsing_data(&self, date: &NaiveDate) -> anyhow::Result<()> {
        let mut client = get_ch_pool().get_handle().await?;
        let date_str = date.format("%Y-%m-%d").to_string();
        let ap = self.tconf.acbl_procs.as_ref().unwrap();
        let sql_str = format!(
            "select count() from {} where DTID = toDate('{date_str}')",
            ap.acbl_table_name
        );
        let block = client.query(&sql_str).fetch_all().await?;
        let count: u64 = block.rows().next().unwrap().get(0)?;
        if count == 0 {
            return Ok(());
        }
        let mut col_names: Vec<&str> = Vec::new();
        for (k, _v) in ap.col_map.iter() {
            col_names.push(k);
        }
        col_names.push("DIVI.DPID");
        col_names.push("DIVI.EMID");
        col_names.push("DIVI.DSVL");
        let col_names_str = col_names.join(",");
        let sql_str = format!(
            "insert into {}({col_names_str},SIGN) select {col_names_str},-1 from {} where DTID = toDate('{date_str}')",
            ap.acbl_table_name, ap.acbl_table_name
        );
        client.execute(&sql_str).await?;
        Ok(())
    }
    ///导入当日数据
    pub async fn run(&self, date: &NaiveDate) -> anyhow::Result<()> {
        if self.tconf.acbl_procs.is_none() {
            return Ok(());
        }
        let mut incr_col: Option<&str> = None;
        let mut ipdt_col: Option<&str> = None;
        for (k, v) in self.tconf.db_calc_cols.iter() {
            if v == "#INCR" {
                incr_col = Some(k);
            }
            if v == "#IPDT" {
                ipdt_col = Some(k);
            }
        }
        if incr_col.is_none() {
            return Err(anyhow!(
                "导入文件{}的db_calc_cols配置中未找到自增列#INCR",
                self.tconf.file_name
            ));
        }
        if ipdt_col.is_none() {
            return Err(anyhow!(
                "导入文件{}的db_calc_cols配置中未找到数据行版本列#IPDT",
                self.tconf.file_name
            ));
        }
        self.collapsing_data(date).await?;
        let incr_col = incr_col.unwrap();
        let ipdt_col = ipdt_col.unwrap();
        let date_str = date.format("%Y-%m-%d").to_string();
        let ap = self.tconf.acbl_procs.as_ref().unwrap();
        let mut cols_str1 = String::new();
        let mut cols_str2 = String::new();
        let mut conds: Vec<String> = vec![];
        for (k, v) in ap.col_map.iter() {
            cols_str1.push_str(k);
            if v == incr_col {
                cols_str2.push_str(v);
            } else {
                if v == "#DATE" {
                    cols_str2.push_str(&format!("toDate('{date_str}')"));
                } else {
                    let exp = format!("argMax({},{})", v, ipdt_col);
                    cols_str2.push_str(&exp);
                    if self.tconf.acbl_col_names.contains(v) {
                        conds.push(format!("{}<>0", exp));
                    }
                }
            }
            cols_str1.push(',');
            cols_str2.push(',');
        }
        cols_str1.push_str("DIVI.DPID,DIVI.EMID,DIVI.DSVL,SIGN");
        let dfc=&self.tconf.divi_fact_convs;
        let (with_str, sel_cols) = get_divi_cols(dfc,date, Some(ipdt_col)).await?;
        cols_str2.push_str(&sel_cols);
        cols_str2.push_str(",1");
        let where_str = conds.join(" or ");
        let having_cause = if let Some(hc) = &ap.having_cause {
            hc.as_str().replace("{date}", &date_str)
        } else {
            where_str
        };
        let sql_str=format!("with {with_str} insert into {}({cols_str1}) select {cols_str2} from {} group by {incr_col} having {having_cause}",
        ap.acbl_table_name,self.tconf.table_name);
        let mut client = get_ch_pool().get_handle().await?;
        client.execute(sql_str).await?;
        Ok(())
    }
}

pub async fn get_divi_cols(
    dfc: &Vec<DiviFactConv>,
    date: &NaiveDate,
    ipdt_col: Option<&str>,
) -> anyhow::Result<(String, String)> {
    let mut withs: Vec<String> = vec![];
    let mut sel_dpids: Vec<String> = vec![];
    let mut sel_emids: Vec<String> = vec![];
    let mut sel_dsvls: Vec<String> = vec![];
    if dfc.len() == 0 {
        return Err(anyhow!(
            "divi_fact_convs配置不能为空"
        ));
    }
    for (i, dfc) in dfc.iter().enumerate() {
        let dt = &dfc.divi_type;
        let dj = DiviJoin::new(dt.clone());
        let jname = dj.get_table(date, false).await?;
        let key_col_name = if let Some(ipdt_col) = ipdt_col {
            format!("argMax({},{ipdt_col})", dfc.key_col_name)
        } else {
            dfc.key_col_name.clone()
        };
        withs.push(format!(
            "joinGet('{jname}','DPIPS',{key_col_name}) AS DPIDS{i}"
        ));
        withs.push(format!(
            "joinGet('{jname}','EMIDS',{key_col_name}) AS EMIDS{i}"
        ));
        withs.push(format!(
            "joinGet('{jname}','DSVLS',{key_col_name}) AS DSVLS{i}"
        ));
        if dfc.to_dept_public {
            let dept_col = if let Some(ipdt_col) = ipdt_col {
                format!("argMax({},{ipdt_col})", dfc.dept_col)
            } else {
                dfc.dept_col.clone()
            };
            withs.push(format!("{dept_col} AS PDPID{i}"));
            withs.push(format!(
                "dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64({dept_col})) AS PEMID{i}"
            ));
        } else {
            withs.push(format!("0 AS PDPID{i}"));
            withs.push(format!("0 AS PEMID{i}"));
        }
        if dfc.where_col_name.is_some() && dfc.where_col_val.is_some() {
            let cname = if let Some(ipdt_col) = ipdt_col {
                format!("argMax({},{ipdt_col})", dfc.where_col_name.as_ref().unwrap())
            } else {
                dfc.where_col_name.as_ref().unwrap().to_owned()
            };
            let val = dfc.where_col_val.as_ref().unwrap();
            sel_dpids.push(format!(
                "{cname}='{val}',if(DPIDS{i}=[],[PDPID{i}],DPIDS{i})"
            ));
            sel_emids.push(format!(
                "{cname}='{val}',if(EMIDS{i}=[],[PEMID{i}],EMIDS{i})"
            ));
            sel_dsvls.push(format!("{cname}='{val}',if(DSVLS{i}=[],[1],DSVLS{i})"));
        } else {
            sel_dpids.push(format!("if(DPIDS{i}=[],[PDPID{i}],DPIDS{i})"));
            sel_emids.push(format!("if(EMIDS{i}=[],[PEMID{i}],EMIDS{i})"));
            sel_dsvls.push(format!("if(DSVLS{i}=[],[1],DSVLS{i})"));
        }
    }
    let with_str = withs.join(",");
    let sel_cols = if sel_emids.len() == 1 {
        format!("{},{},{}", sel_dpids[0], sel_emids[0], sel_dsvls[0])
    } else {
        format!(
            "multiIf({},[0]),multiIf({},[0]),multiIf({},[1])",
            sel_dpids.join(","),
            sel_emids.join(","),
            sel_dsvls.join(",")
        )
    };
    Ok((with_str, sel_cols))
}
